<?php

/*************************************************

morecache.php - easily display mysql query results with paging, tabular layout, etx

home page: www.samscripts.com/scripts/mysqltemplate
support: support.samscripts.com/index.php

licence: freeware / optionally giftware ( www.samscripts.com/donate.php )
author: Sam Yapp
date: 8th September 2002

documentation and examples at www.samscripts.com

*************************************************/

class mysqltemplate{

	var $cache = 0;

	var $queriesdone = 0;
	var $queriescached = 0;
	var $totalqueries = 0;

	var $connection;
	var $database;
	var $username;
	var $password;
	var $host;

	var $errors;
	var $header;
	var $footer;
	var $template;

	var $pagesize = 0;
	var $currentpage = 1;
	var $nextpage = 1;
	var $prevpage = 1;
	var $pagelist = "";

	var $tableorder = "columns";
	var $columns = 1;

	var $connection = 0;
	var $host = "";
	var $user = "";
	var $password = "";
	var $database = "";

	var $replace_object = "";
	var $replace_function = "";

	var $headervars = array();
	var $footervars = array();
	var $rowvars = array();

	var $cachename = "";
	var $cacheexpiry = 3600;

	var $queries = array();

	function setcallback($args){
		if( !is_array($args) ){
			$this->replace_object = 0;
			$this->replace_function = $args;
		}else{
			$this->replace_object = &$args[0];
			$this->replace_function = $args[1];
		}
	}


	function mysqltemplate($connection = 0, $database =  MYSQL_DB, $host = MYSQL_SERVER, $user = MYSQL_USER, $password = MYSQL_SENHA){
		$this->connection = $connection;
		$this->database = $database;
		$this->host = $host;
		$this->user = $user;
		$this->password = $password;
		$this->settemplate();
	}

	function settemplate($template="", $resetfields = true){
		if( $resetfields){
			unset($this->replace_object);
			$this->replace_object = "";
			$this->replace_function = "";
			$this->pagesize = 0;
			$this->currentpage = 1;
			$this->nextpage = 1;
			$this->prevpage = 1;
			$this->pagelist = "";

			$this->tableorder = "columns";
			$this->columns = 1;

			$this->cachename = "";
		}
		$this->headervars = array();
		$this->footervars = array();
		$this->template =& $template;
		$this->compiled = false;
		$this->rows = array();
		$this->rowcount = 0;
		$this->cnt = 0;
		$this->groupheader = "";
		$this->groupfooter = "";
	}

	function getmicrotime(){
		list($usec, $sec) = explode(" ",microtime());
		return ((float)$usec + (float)$sec);
	} 

	function connect(){
		if( !$this->connection ){
			if( isset($this->profiler) ) $this->profiler->profile("connect to database");
			$start = $this->getmicrotime();
			$this->connection = @mysql_connect($this->host, $this->user, $this->password)or die(mysql_error());
			if( isset($this->profiler) ) $this->profiler->profile("finished connect to database");
			$this->connecttime = $this->getmicrotime() - $start;
			if( !$this->connection ){
				return false;
			}
			if( isset($this->profiler) ) $this->profiler->profile("select database");
			$selected =@mysql_select_db($this->database, $this->connection);
			if( isset($this->profiler) ) $this->profiler->profile("finish select database");
			$this->selectdbtime = $this->getmicrotime() - $start + $this->connecttime;
			$this->connectselecttime = $this->selectdbtime + $this->connecttime;
			if( !$selected ) return false;
		}
		return true;
	}

	function print_debug(){
		echo '<table class="box" align="center"><tr><td><b>Query</b></td><td><b>Time Taken</b></td><td align="center"><b>From Cache?</b></td><td align="center"><b>Internal?</b></td></tr>';
		echo '<tr><td>Connect To MySQL</td><td>'.$this->connecttime.'</td><td>&nbsp;</td><td>&nbsp;</td></tr>';
		echo '<tr><td>Select Database</td><td>'.$this->selectdbtime.'</td><td>&nbsp;</td><td>&nbsp;</td></tr>';
		echo '<tr><td>Connect and Select Database</td><td>'.$this->connectselecttime.'</td><td>&nbsp;</td><td>&nbsp;</td></tr>';
		for( $i = 0; $i < $this->totalqueries; $i++){
			$q = $this->queries[$i];
			echo '<tr><td>'.htmlspecialchars($q["sql"]).'</td><td>'.$q["timetaken"].'</td><td align="center">'.($q["cached"] == 1 ? 'Yes' : 'No').'</td><td align="center">'.($q["internal"]==1 ? 'Yes' : 'No').'</td></tr>';
		}
		echo '</table>';
	}

	function mysql_query($sql, $internal=0){
		if( !$this->connect() ) return false;
		$this->queriesdone++;
		$this->totalqueries++;
		$this->queries[$this->totalqueries-1]["sql"] = $sql;
		$this->queries[$this->totalqueries-1]["cached"] = 0;
		$this->queries[$this->totalqueries-1]["internal"] = $internal;
		$start = $this->getmicrotime();
		if( isset($this->profiler) ) $this->profiler->profile("query: $sql");
		$result = @mysql_query($sql, $this->connection);
		if( isset($this->profiler) ) $this->profiler->profile("finished query: $sql");
		$this->queries[$this->totalqueries-1]["timetaken"] = $this->getmicrotime()-$start;
		return $result;
	}

	function query($sql){
		if( $this->cache != 0 && $this->cachename != "" ){
			$caching = 1;
			$start = $this->getmicrotime();
			if( isset($this->profiler) ) $this->profiler->profile("checking caching): $sql");
			$update = $this->cache->cache($this->cachename, $this->cacheexpiry);
			if( !$update ){
				if( isset($this->profiler) ) $this->profiler->profile("query (cached): $sql");
				$this->totalqueries++;
				$this->queriescached++;
				$this->queries[$this->totalqueries-1]["internal"] = 0;
				$this->queries[$this->totalqueries-1]["sql"] = $sql;
				$this->queries[$this->totalqueries-1]["cached"] = 1;
				$this->queries[$this->totalqueries-1]["timetaken"] = $this->getmicrotime() - $start;
			}
		}else{
			$caching = 0;
			$update = true;
		}
		if( $update ){
			$start = $this->getmicrotime();
			if( $this->do_query(&$sql)){
				if( $this->columns > 1 ){
					$result = true;
					$this->printquery_tabled();
				}else{
					$result = true;
					$this->printquery();
				}
			}else{
				$result = false;
			}
			if( $caching ){
				$this->cache->stop();
			}
		}else{
			return true;
		}
		return $result;
	}


	function printquery_tabled(){
		$result =& $this->result;

		$cols = $this->columns;
		$method = preg_match("/vert/is", $this->tableorder) ? "vert" : "horiz";

		$headervars =& array_merge(&$this->headervars, &$this->defaultvars);
		$footervars =& array_merge(&$this->footervars, &$this->defaultvars);
		$rowvars =& array_merge(&$this->rowvars, &$this->defaultvars);

		$cnt = $this->numrows;
		$this->printheader(array_keys($headervars), array_values($headervars));
		$numrows = (int)(($cnt + ($cols-1))/ $cols);
		$fullcols = $cnt % $cols;
		if ($fullcols == 0 ) $fullcols = $cols;
		$numxfull = $numrows * $fullcols;
		$colsminusfullcols = $cols - $fullcols;
		$i = 0;
		for( $r = 0; $r < $numrows; $r++){
			$rx = $r * $colsminusfullcols + $numxfull - $fullcols;
			$this->printgroupheader("","");
			for( $col = 0; $col < $cols; $col++){
				if( $method != "horiz" ){
					if( $col < $fullcols ){
						$rownum = $r + ($col * ($numrows));
					}else{
						$rownum = $rx + $col;
					}
					if( $rownum < $cnt ){
						mysql_data_seek($result,$rownum);
						$row = mysql_fetch_assoc($result);
					}else{
						$row = array();
					}
				}else{
					$row = mysql_fetch_assoc($result);
				}
				$i++;
				if( $row ){
					if( $this->replace_function != "" ){
						$func = $this->replace_function;
						if( $this->replace_object != "" ){
							$obj = $this->replace_object;
							$obj->$func(&$row);
						}else{
							$func(&$row);
						}
//						eval($repfunc.'($row);');
					}
					$row["rownumber"] = $i;
					$this->printrow($row);
				}else{
					$this->printerror("emptycell", "", "");
				}
			}
			$this->printgroupfooter("","");
		}
		$this->printfooter(array_keys($footervars), array_values($footervars));
	}

	function printquery(){

		$headervars =& array_merge(&$this->headervars, &$this->defaultvars);
		$footervars =& array_merge(&$this->footervars, &$this->defaultvars);
		$rowvars =& array_merge(&$this->rowvars, &$this->defaultvars);

		$this->printheader(array_keys($headervars), array_values($headervars));

		$i = 0;
		while( $row = mysql_fetch_assoc($this->result) ){
			if( $this->replace_function != "" ){
				$func = $this->replace_function;
				if( $this->replace_object != "" ){
					$obj = $this->replace_object;
					$obj->$func(&$row);
				}else{
					$func(&$row);
				}
//				eval($repfunc.'($row);');
			}
			$i++;
			$row["rownumber"] = $i;
			$this->printrow(array_merge($rowvars, $row));
		}

		$this->printfooter(array_keys($footervars), array_values($footervars));

	}


	function printheader($search="", $replace=""){
		if( is_array($search) )	$search = split(":::", "<:".join(">:::<:", $search).">");
//		echo "search:",$search,"<br>replace:",$replace, "<br>header:",$this->header,"<br>";
		echo str_replace(&$search, &$replace, &$this->header);
	}

	function printfooter($search="", $replace=""){
		if( is_array($search) )	$search = split(":::", "<:".join(">:::<:", $search).">");
		echo str_replace(&$search, &$replace, &$this->footer);
	}

	function printgroupheader($search, $replace){
		if( is_array($search) &&count($search) ) $search = split(":::", "<:".join(">:::<:", $search).">");
		echo str_replace(&$search, &$replace, &$this->groupheader[0]);
	}

	function printgroupfooter($search, $replace){
		if( is_array($search) && count($search) )	$search = split(":::", "<:".join(">:::<:", $search).">");
		echo str_replace(&$search, &$replace, &$this->groupfooter[0]);
	}

	function printerror($errorname, $search, $replace){
		if( is_array($search) )	$search = split(":::", "<:".join(">:::<:", $search).">");
		if( isset($this->errors[$errorname]) ) echo str_replace(&$search, &$replace, &$this->errors[$errorname]);
//		echo join("<br>", $replace);
	}

	function printrow($row){
		eval($this->rows[$this->cnt]);
		$this->cnt++;
		if( $this->cnt == $this->rowcount) $this->cnt = 0;
	}

	function converttemplate(&$t){
		$tnew = 'echo "'.preg_replace('~<:[ ]*?([a-z0-9_]+)[ ]*.*?>~is', '".$row["$1"]."', str_replace('"', '\"',$t)).'";';
		return $tnew;
	}

	function do_query(&$sql){
		if( !$this->compiled ){
			$this->splittemplate();
			$this->compiletemplate();
		}
		if( !$this->connect() ) return $this->printerror("dberror", array("message", "sql"), array(mysql_error(), $sql));
		if( $this->pagesize > 0 ){
			if( preg_match("/group by (.*?) /is", $sql, $match) ){
				$selectme = "COUNT(DISTINCT ".trim($match[1]).")";
			}else{
				$selectme = "COUNT(*)";
			}
			preg_match_all("/select (.*?) from (.*)/is", $sql, $matches);
			$fieldstr = $matches[1][0];
			$conditions = $matches[2][0];
			$len = strlen($fieldstr);
			$fields = array();
			$brackets = 0;
			$quotes = 0;
			$field = "";
			for( $i = 0; $i < $len; $i++){
				$dontadd = false;
				switch ($fieldstr[$i]){
					case "'":
						$quotes = 1 - $quotes;
						break;
					case "(":
						$brackets++;
						break;
					case ")":
						$brackets--;
						break;
					case ",":
						if( !$quotes && $brackets == 0 ){
							$fields[] = trim($field);
							$field = "";
							$dontadd = true;
						}
						break;
				}
				if( !$dontadd ) $field.=$fieldstr[$i];
			}
			if( trim($field) != "" ) $fields[] = trim($field);

			for( $i = 0; $i < count($fields); $i++){
				if( preg_match("/(.*) as ([\w\d_-]*)/is", $fields[$i], $matches) ){
					$conditions = preg_replace("/[^\.]\b".$matches[2]."\b/is", $matches[1], $conditions);
				}
			}
			$conditions = preg_replace("/ order by.*/is", "", $conditions);
			$conditions = preg_replace("/ group by.*/is", "", $conditions);

			$res = $this->mysql_query("SELECT $selectme FROM $conditions", 1);

			if( !$res )return  $this->printerror("dberror", array("message", "sql"), array(mysql_error(), $sql));

			$this->rowtotal = mysql_result($res, 0,0);
			$this->pagecount = (int)(($this->rowtotal + $this->pagesize - 1) / $this->pagesize);
			if( $this->currentpage < 1 ) $this->currentpage = 1;
			if( $this->currentpage > $this->pagecount) $this->currentpage = $this->pagecount;
			$this->firstrow = $this->pagesize * ($this->currentpage - 1) + 1;
			if( $this->firstrow > $this->rowtotal ) $this->firstrow = $this->rowtotal - $this->pagesize;
			if( $this->firstrow < 0 ) $this->firstrow = 0;
			$this->lastrow = $this->firstrow + $this->pagesize - 1;
			if( $this->lastrow > $this->rowtotal ) $this->lastrow = $this->rowtotal;
			$sql .= " LIMIT ".($this->firstrow-1).", ".$this->pagesize;
			if( $this->currentpage == $this->pagecount ){
				$this->nextpage = $this->pagecount;
				$nextrep = "";
			}else{
				$this->nextpage = $this->currentpage+1;
				$nextrep = '$1';
			}
			if( $this->currentpage == 1 ){
				$this->prevpage = 1;
				$prevrep = "";
			}else{
				$this->prevpage = $this->currentpage - 1;
				$prevrep = '$1';
			}
			$pagelists = array();
			for( $i = 1; $i <= $this->pagecount; $i++){
				if( $i == $this->currentpage ){
					$pagelists[$i-1] = str_replace("<:page>", $i, $this->pagelistthispageformat);
				}else{
					$pagelists[$i-1] = str_replace("<:page>", $i, $this->pagelistformat);
				}
			}
			$this->pagelist = join("", $pagelists);
			$this->header = preg_replace(array("~<:ifnext>(.*?)</:ifnext>~is","~<:ifprev>(.*?)</:ifprev>~"),
													array($nextrep, $prevrep), $this->header);
			$this->footer = preg_replace(array("~<:ifnext>(.*?)</:ifnext>~is","~<:ifprev>(.*?)</:ifprev>~"),
													array($nextrep, $prevrep), $this->footer);
		}

		$this->result = $this->mysql_query($sql, 1);
		if( $this->result ){
			$this->numrows = mysql_num_rows($this->result);
			if( $this->pagesize == 0 ){
				$this->rowtotal = $this->lastrow = $this->numrows;
				$this->firstrow = 1;
				$this->pagecount = 1;
				$this->currentpage = 1;
			}
			if( $this->numrows == 0 ){
				$this->printerror("norecords", "", "");
				return false;
			}
			$this->defaultvars = array("rowcount"=>$this->numrows, "totalrows"=>$this->rowtotal, "currentpage"=>$this->currentpage,
									"pagesize"=>$this->pagesize, "pagecount"=>$this->pagecount, "firstrow"=>$this->firstrow,
									"lastrow"=>$this->lastrow, "nextpage"=>$this->nextpage, "prevpage"=>$this->prevpage,"pagelist"=>$this->pagelist);
			return true;
		}else{
			$this->printerror("dberror", array("message", "sql"), array(mysql_error(),$sql));
			return false;
		}
	}

	function compileheaders(){
		$this->ghcompiled = array();
		$this->gfcompiled = array();
		for( $i = 0; $i < count($this->groupheader); $i++){
			$this->ghcompiled[$i] = $this->converttemplate($this->groupheader[$i]);
		}
		for( $i = 0; $i < count($this->groupfooter); $i++){
			$this->gfcompiled[$i] = $this->converttemplate($this->groupfooter[$i]);
		}
		$this->ghcount = count($this->ghcompiled);
		$this->gfcount = count($this->gfcompiled);
	}

	function compiletemplate(){
		$this->rows = array();
		$found = preg_match_all('~<:row>(.*?)</:row>~is', $this->template, $temp);
		if( $found ){
			for( $i =0; $i < count($temp[1]); $i++){
				$this->rows[] = $this->converttemplate($temp[1][$i]);
			}
		}else{
			$this->rows[] = $this->converttemplate($this->template);
		}
		$this->rowcount = count($this->rows);
		$this->compiled = true;
	}

	function splittemplate(){
		$tags = array("pagelistformat", "header", "footer", "error",  "groupheader", "groupfooter");
		$tags = join("|", $tags);
		$regex = "~<:($tags)[=]?([a-z0-9_]*)[ ]*>(.*?)</:\\1[ =]?\\2?[ ]*>[\n]?~is";
		if( preg_match_all($regex, $this->template, $temps) ){
			$out = array();
			for( $i = 0; $i < count($temps[1]); $i++){
				if( $temps[2][$i] == "" ){
					$out[$temps[1][$i]][] = $temps[3][$i];
				}else{
					$out[$temps[1][$i]][$temps[2][$i]] = $temps[3][$i];
				}
			}
		}
		$this->template =& preg_replace($regex, "", &$this->template);
		$this->errors = isset($out["error"]) ? $out["error"] : array();
		$this->header = isset($out["header"][0]) ? $out["header"][0] : "";
		$this->footer = isset($out["footer"][0]) ? $out["footer"][0] : "";
		$this->groupheader = isset($out["groupheader"]) ? $out["groupheader"] : array("");
		$this->groupfooter = isset($out["groupfooter"]) ? $out["groupfooter"] : array("");
		$this->plformat = isset($out["pagelistformat"]) ? $out["pagelistformat"][0] : "";
		global $REQUEST_URI, $HTTP_SERVER_VARS;
		$query = isset($HTTP_SERVER_VARS["QUERY_STRING"]) ? $HTTP_SERVER_VARS["QUERY_STRING"] : "";
		$query = preg_replace("/[&|?]*currentpage=[0-9]*/", "", $query);
		$this->pagelistformat = '<a href="'.$REQUEST_URI.'?currentpage=<:page>&'.$query.'"><:page></a> ';
		$this->pagelistthispageformat = "<b><:page></b> ";
		if( $this->plformat != "" ){
			if( preg_match("~<:thispage>(.*?)</:thispage>~is", $this->plformat, $match) ){
				$this->pagelistthispageformat = $match[1];
				$this->plformat = preg_replace("~<:thispage>.*</:thispage>~is", "", $this->plformat);
			}
			$this->pagelistformat = $this->plformat;
		}
	}

}

?>